package model;

import java.util.List;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;

public class DatabaseConnection {

	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://127.0.0.1:3306/";
	private String user = "root"; 
	private String password = "12345qwert";
	//String db = "bank123";
	private Connection conn;
	
	private PreparedStatement insertUserStatement;
	private PreparedStatement updateUserStatement;
	private PreparedStatement deleteUserStatement;
	private PreparedStatement selectUserStatement;

	private PreparedStatement insertGroupStatement;
	private PreparedStatement updateGroupStatement;
	private PreparedStatement deleteGroupStatement;
	private PreparedStatement selectGroupStatement;
	/**/
	private PreparedStatement joinStatement;
	private PreparedStatement quitStatement;
	private PreparedStatement assignStatement;
	private PreparedStatement selectMembersStatement;
	private PreparedStatement selectCommunitiesStatement;
	
	public PreparedStatement insertImage;
	public PreparedStatement selectImage;
	public PreparedStatement updateImage;
	
	
	public DatabaseConnection(String db) {
		// TODO Auto-generated constructor stub
		try {
	    	Class.forName(driver);
	    	url += db;
	    	this.conn = DriverManager.getConnection(url, user, password);
	    	if(!conn.isClosed())
	    		System.out.println("Succeeded connecting to the Database "+db+"!");
	    }catch(Exception e){
	    	System.out.println("failed connect to the Database "+db+"!");
	    }
	}
	
	public void prepare() throws SQLException {
		String sql;

		sql = "insert into users (id, name, password, sex,face) values (?, ?, ?, ?,0);";
		insertUserStatement = conn.prepareStatement(sql);

		sql = "update users set name=?, password=?, sex=? face=? where id=?";
		updateUserStatement =  conn.prepareStatement(sql);

		sql = "delete from users where id=?";
		deleteUserStatement = conn.prepareStatement(sql);

		sql = "select * from users where id=?";
		selectUserStatement = conn.prepareStatement(sql);

		sql = "insert into groups (id, name, former,face) values (?,?, ?, ?);";
		insertGroupStatement = conn.prepareStatement(sql);

		sql = "update groups set name=?, former=? face=? where id=?";
		updateGroupStatement =  conn.prepareStatement(sql);

		sql = "delete from groups where id=?";
		deleteGroupStatement = conn.prepareStatement(sql);

		sql = "select * from groups where id=?";
		selectGroupStatement = conn.prepareStatement(sql);
		/**/
		sql = "insert into gtou (user_id, group_id, role) values (?, ?, ?);";
		joinStatement= conn.prepareStatement(sql);

		sql = "delete from gtou where user_id=? and group_id=?;";
		quitStatement = conn.prepareStatement(sql);

		sql = "update gtou set role=? where user_id=? and group_id=?";
		assignStatement = conn.prepareStatement(sql);
		
		sql = "select * from gtou where group_id=?";
		selectMembersStatement = conn.prepareStatement(sql);
		
		sql = "select * from gtou where user_id=?";
		selectCommunitiesStatement = conn.prepareStatement(sql);
		
		sql = "select * from image where id=?;";
		selectImage = conn.prepareStatement(sql);
		
		sql = "insert into image (id,img) values(?,?);";
		insertImage = conn.prepareStatement(sql);
		
		sql = "update image set img=? where id=?";
		updateImage = conn.prepareStatement(sql);
	}
	
	public String insertUser(User user) throws SQLException{
		String id = DatabaseConnection.getUUID(this);
		System.out.println(id);
    	insertUserStatement.setString(1, id);
    	insertUserStatement.setString(2, user.getName());
    	insertUserStatement.setString(3, user.getPassword());
    	insertUserStatement.setString(4, user.getSex());
    	insertUserStatement.setLong(5, user.getFace());
    	insertUserStatement.execute();
    	return id;
	}
	
	public String insertGroup(Group group) throws SQLException{
		String id = DatabaseConnection.getUUID(this);
		insertGroupStatement.setString(1, id);
		insertGroupStatement.setString(2, group.getName());
    	insertGroupStatement.setString(3, group.getFormer());
    	insertGroupStatement.setLong(5, group.getFace());
    	insertGroupStatement.execute();
    	return id;
	}
	
	public void deleteUser(User user) throws SQLException{
		deleteUserStatement.setString(1, user.getId());
		deleteUserStatement.execute();
	}
	
	public void deleteGroup(Group group) throws SQLException{
		deleteGroupStatement.setString(1, group.getId());
		deleteGroupStatement.execute();
	}
	
	public void updateUser(User user) throws SQLException {
		updateUserStatement.setString(1, user.getName());
		updateUserStatement.setString(2, user.getPassword());
		updateUserStatement.setString(3, user.getSex());
		updateUserStatement.setString(4, user.getId());
		updateUserStatement.setLong(5, user.getFace());
		updateUserStatement.execute();
	}
	
	public void updateGroup(Group group) throws SQLException {
		updateGroupStatement.setString(1, group.getName());
		updateGroupStatement.setString(2, group.getFormer());
		updateGroupStatement.setString(3, group.getId());
		updateGroupStatement.setLong(4, group.getFace());
		updateGroupStatement.execute();
	}
	
	public User getUser(String id) throws SQLException {
		User user = new User();

		selectUserStatement.setString(1, id);
		ResultSet result = selectUserStatement.executeQuery();

		if (result.next()) {
			user.setId(id);;
			user.setName(result.getString("name"));
			user.setPassword(result.getString("password"));
			user.setSex(result.getString("sex"));
			user.setFace(result.getLong("face"));
			return user;
		}
		return null;
	}
	
	public ArrayList<User> getUsers(String id) throws SQLException {
		User user;
		ArrayList<User> users = new ArrayList<>();
		selectUserStatement.setString(1, id);
		ResultSet result = selectUserStatement.executeQuery();

		while (result.next()) {
			user = new User();
			user.setId(id);;
			user.setName(result.getString("name"));
			user.setPassword(result.getString("password"));
			user.setSex(result.getString("sex"));
			user.setFace(result.getLong("face"));
			users.add(user);
			return users;
		}
		return null;
	}
	
	public Group getGroup(String id) throws SQLException {
		Group group = new Group();

		selectGroupStatement.setString(1, id);
		ResultSet result = selectGroupStatement.executeQuery();

		if (result.next()) {
			group.setId(id);
			group.setName(result.getString("name"));
			group.setFormer(result.getString("former"));
			group.setFace(result.getLong("face"));
			return group;
		}
		return null;
	}
	
	public void join(String user_id, String group_id, String role) throws SQLException {
		User user = getUser(user_id);
		Group group = getGroup(group_id);
		joinStatement.setString(1, user.getId());
		joinStatement.setString(2, group.getId());
		joinStatement.setString(3, role);
		joinStatement.execute();
	}

	public void quit(String user_id, String group_id) throws SQLException {
		User user = getUser(user_id);
		Group group = getGroup(group_id);
		quitStatement.setString(1, user.getId());
		quitStatement.setString(2, group.getId());
		quitStatement.execute();
	}

	public void assign(String user_id, String group_id, String role) throws SQLException {
		User user = getUser(user_id);
		Group group = getGroup(group_id);
		assignStatement.setString(1, role);
		assignStatement.setString(2, user.getId());
		assignStatement.setString(3, group.getId());
		assignStatement.execute();
	}
	
	public List<User> getMembers(String id) throws SQLException {
		List<User> members = new ArrayList<User>();
		User user;

		selectMembersStatement.setString(1, id);
		ResultSet result = selectMembersStatement.executeQuery();

		while (result.next()) {
			user = this.getUser(result.getString("user_id"));
			members.add(user);
		}
		return members;
	}
	
	public List<Group> getCommunities(String id) throws SQLException {
		List<Group> communities = new ArrayList<Group>();
		Group group;

		selectCommunitiesStatement.setString(1, id);
		ResultSet result = selectCommunitiesStatement.executeQuery();

		while (result.next()) {
			group = this.getGroup(result.getString("group_id"));
			communities.add(group);
		}
		return communities;
	}
	
	
	
	public static String getUUID(Object obj) {
		String first = String.valueOf(obj.hashCode() % 10000);
		String second = String.valueOf(System.currentTimeMillis() % 10000);
		String third = String.valueOf((int) (Math.random() * 1000));
		return first + second + third;
	}

}
